#! /bin/bash

if [ -n "$1" ]; then
   data_date="$1"
else
    data_date=$(date -d  "-1 days" +%F )
fi


Tmp_event_log_cleaned_sql="drop table if exists jtp_app_tmp.tmp_event_log_cleaned;
                           create  table if not exists jtp_app_tmp.tmp_event_log_cleaned as
                           select * from jtp_app_ods.ods_event_app_log
                           where dt = '$data_date' and deviceid is not null  and length(trim(deviceid)) != 0 and
                               eventid is not null  and length(trim(eventid)) != 0 and
                               sessionid is not null  and length(trim(sessionid)) != 0 and
                               properties is not null  and size(properties) != 0
                             and account is not null  and length(trim(account)) != 0 ;"
  
Tmp_event_log_session_sql="DROP TABLE IF EXISTS jtp_app_tmp.tmp_event_log_session ;
                           create  table jtp_app_tmp.tmp_event_log_session as
                           with t as (
                               select account, appid, appversion, carrier, deviceid, devicetype, eventid, ip, latitude, longitude, nettype, osname, osversion, properties,
                                      releasechannel, resolution, sessionid, \`timestamp\`, dt,lag(\`timestamp\`,1,0) over (partition by sessionid order by \`timestamp\` ) lagtime,
                                      (\`timestamp\`-lag(\`timestamp\`,1,0) over (partition by sessionid order by \`timestamp\` )) difftime,
                                      (\`timestamp\`-lag(\`timestamp\`,1,0) over (partition by sessionid order by \`timestamp\` ))/1000/60 fz,
                                      if((\`timestamp\`-lag(\`timestamp\`,1,0) over (partition by sessionid order by \`timestamp\` ))/1000/60 >= 10,\`timestamp\`,null) starttime
                               from jtp_app_tmp.tmp_event_log_cleaned
                           ), t1 as (
                               select
                                   account, appid, appversion, carrier, deviceid, devicetype, eventid, ip, latitude, longitude, nettype, osname, osversion,
                                   properties, releasechannel,
                                   resolution, sessionid, \`timestamp\`,
                                   concat(deviceid,'_',last_value(starttime,true) over(partition by deviceid order by \`timestamp\`)) new_session_id,dt
                               from t
                           )
                           select * from t1;"

Tmp_event_log_dic_sql="drop table if exists jtp_app_tmp.tmp_event_log_dic;
                       create table jtp_app_tmp.tmp_event_log_dic as
                       with t0 as (
                           select *,GeoHash(longitude,latitude) geo_hash from jtp_app_tmp.tmp_event_log_session
                       )
                       select t0.account, t0.appid, t0.appversion, t0.carrier, t0.deviceid, t0.devicetype, t0.eventid, t0.ip, t0.latitude, t0.longitude, t0.nettype,
                              t0.osname, t0.osversion, t0.properties, t0.releasechannel,
                              t0.resolution, t0.sessionid, t0.\`timestamp\`, t0.new_session_id,
                              d.province,d.city,d.district,d.street
                               ,t0.geo_hash, t0.dt from t0 inner join jtp_app_dim.dim_md_areas d
                                                                      on d.geo_hash = t0.geo_hash where d.dt='$data_date';"

Dwd_event_log_detail_sql="INSERT OVERWRITE TABLE jtp_app_dwd.dwd_event_log_detail PARTITION (dt = '$data_date')
                          SELECT
                              account, appid, appversion, carrier, deviceid, devicetype, eventid
                               , ip, latitude, longitude, province, city, district, nettype
                               , osname, osversion, properties, releasechannel, resolution, new_session_id, \`timestamp\`
                          FROM jtp_app_tmp.tmp_event_log_dic;"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${Tmp_event_log_cleaned_sql}${Tmp_event_log_session_sql}${Tmp_event_log_dic_sql}${Dwd_event_log_detail_sql}"